package com.demo.springMvcDemo.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.springframework.stereotype.Repository;
import com.demo.springMvcDemo.dao.IEmployeeDAO;
import com.demo.springMvcDemo.model.Employee;

@Repository
public class EmployeeDAOImpl implements IEmployeeDAO {
	// 查询所有信息
	@Override
	public List<Employee> queryList() {
		List<Employee> list = new ArrayList<Employee>();
		try {
			Connection connection = getConn();
			String sql = "select * from user";
			PreparedStatement ps = connection.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Employee em = new Employee();
				em.setId(rs.getInt(1));
				em.setName(rs.getString(2));
				em.setAge(rs.getInt(3));
				em.setSalary(rs.getDouble(4));
				em.setHireDate(rs.getDate(5));
				list.add(em);
			}
			ps.close();
			connection.close();
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	// 删除数据
	@Override
	public void deleteObject(Integer id) {
		try {
			Connection connection = getConn();
			String sql = "delete from user where id = ?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
			ps.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 插入数据
	@Override
	public void insertObject(Employee employee) {
		try {
			Connection connection = getConn();
			String sql = "insert into user(name,age,salary,hireDate) value (?,?,?,?)";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, employee.getName());
			ps.setInt(2, employee.getAge());
			ps.setDouble(3, employee.getSalary());
			//日期格式转换
			java.util.Date utildate = employee.getHireDate();
			java.sql.Date hireDate = new java.sql.Date(utildate.getTime());
			ps.setDate(4, hireDate);
			ps.executeUpdate();
			ps.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 更新
	@Override
	public void updateObject(Employee employee) {
		try {
			Connection connection = getConn();
			String sql = "update user t set t.name=?, t.age=?, t.salary=?, t.hireDate=? where t.id=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, employee.getName());
			ps.setInt(2, employee.getAge());
			ps.setDouble(3, employee.getSalary());
			java.util.Date utildate = employee.getHireDate();
			java.sql.Date hireDate = new java.sql.Date(utildate.getTime());
			ps.setDate(4, hireDate);
			ps.setInt(5, employee.getId());
			ps.executeUpdate();
			ps.close();
			connection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	// 查询单条数据
	@Override
	public Employee queryObject(Integer id) {
		Employee em = new Employee();
		try {
			Connection connection = getConn();
			String sql = "select * from user where id = ?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				em.setId(rs.getInt(1));
				em.setName(rs.getString(2));
				em.setAge(rs.getInt(3));
				em.setSalary(rs.getDouble(4));
				em.setHireDate(rs.getDate(5));
			}
			ps.close();
			connection.close();
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return em;
	}

	// 公共连接数据库部分
	private Connection getConn() throws ClassNotFoundException, SQLException {
		Class.forName("com.mysql.jdbc.Driver");
		Connection connection = (Connection) DriverManager.getConnection(
				"jdbc:mysql://localhost:3306/mana1?useUnicode=true&amp;characterEncoding=UTF-8", "root", "");
		return connection;
	}

}
